﻿/*----------------------------------------------------------------
* 项目名称 ：OrBitDataHarvester.FileHelper
* 项目描述 ：
* 类 名 称 ：Excel
* 类 描 述 ：
* 所在的域 ：DESKTOP-AMCPCSU
* 命名空间 ：OrBitDataHarvester.FileHelper
* 机器名称 ：DESKTOP-AMCPCSU 
* 作    者 ：Corbyn
* 创建时间 ：2023-05-25 09:52:07
* 更新时间 ：2023-05-25 09:52:07
* 版 本 号 ：v1.0.0.0
*/

using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OrBitDataModule.FileHelper
{
    public static class ExcelUtils
    {
        /// <summary>
        /// 获取Excel文件所有Sheet
        /// </summary>
        /// <param name="path">Excel文件路径</param>
        /// <returns></returns>
        public static List<string> GetSheetNames(string path)
        {
            List<string> sheetNames = new List<string>();
            using (Workbook workbook = new Workbook(path))
            {
                foreach (Worksheet sheet in workbook.Worksheets)
                {
                    sheetNames.Add(sheet.Name);
                }
            }
            return sheetNames;
        }

        /// <summary>
        /// 导出dataTable，默认第一个Sheet
        /// </summary>
        /// <param name="path">Excel文件路径</param>
        /// <param name="useHeaderRow">第一行是否为表头</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string path, bool useHeaderRow = true)
        {
            return GetDataTable(path, useHeaderRow, null, null);
        }

        /// <summary>
        /// 导出dataTable
        /// </summary>
        /// <param name="path">Excel文件路径</param>
        /// <param name="useHeaderRow">第一行是否为表头</param>
        /// <param name="sheetName">表格名称</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string path, bool useHeaderRow, string sheetName)
        {
            return GetDataTable(path, useHeaderRow, sheetName, null);
        }

        /// <summary>
        /// 导出dataTable
        /// </summary>
        /// <param name="path">Excel文件路径</param>
        /// <param name="useHeaderRow">第一行是否为表头</param>
        /// <param name="sheetName">表格名称</param>
        /// <param name="startCell">开始读取的位置，比如第一行第一列为：A1</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string path, bool useHeaderRow, string sheetName, string startCell)
        {
            DataTable dataTable = new DataTable();
            using (Workbook workbook = new Workbook(path))
            {
                Worksheet sheet;
                if (!string.IsNullOrEmpty(sheetName))
                    sheet = workbook.Worksheets[sheetName];
                else
                    sheet = workbook.Worksheets[0];

                Cells cells = sheet.Cells;
                int startRowIdx = 0;
                int startColIdx = 0;

                if (!string.IsNullOrEmpty(startCell))
                {
                    CellsHelper.CellNameToIndex(startCell, out startRowIdx, out startColIdx);
                }

                for (int col = startColIdx; col < cells.MaxDataColumn + 1; col++)
                {
                    DataColumn column = new DataColumn(useHeaderRow ? cells[startRowIdx, col].StringValue : "Column" + col);
                    dataTable.Columns.Add(column);
                }

                for (int row = startRowIdx + 1; row < cells.MaxDataRow + 1; row++)
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int col = startColIdx; col < cells.MaxDataColumn + 1; col++)
                    {
                        dataRow[col - startColIdx] = cells[row, col].StringValue;
                    }
                    dataTable.Rows.Add(dataRow);
                }
            }
            return dataTable;
        }

        /// <summary>
        /// 导出DataSet
        /// </summary>
        /// <param name="path">Excel文件路径</param>
        /// <param name="useHeaderRow">第一行是否为表头</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string path, bool useHeaderRow = true)
        {
            DataSet dataSet = new DataSet();
            using (Workbook workbook = new Workbook(path))
            {
                foreach (Worksheet sheet in workbook.Worksheets)
                {
                    DataTable dataTable = new DataTable(sheet.Name);
                    Cells cells = sheet.Cells;
                    int rowCount = cells.MaxDataRow + 1;
                    int colCount = cells.MaxDataColumn + 1;

                    int startRowIdx = useHeaderRow ? 0 : 1;

                    for (int col = 0; col < colCount; col++)
                    {
                        DataColumn column = new DataColumn(cells[startRowIdx, col].StringValue);
                        dataTable.Columns.Add(column);
                    }

                    for (int row = startRowIdx + 1; row < rowCount; row++)
                    {
                        DataRow dataRow = dataTable.NewRow();
                        for (int col = 0; col < colCount; col++)
                        {
                            dataRow[col] = cells[row, col].StringValue;
                        }
                        dataTable.Rows.Add(dataRow);
                    }

                    dataSet.Tables.Add(dataTable);
                }
            }
            return dataSet;
        }

        /// <summary>
        /// 读取csv文件
        /// </summary>
        /// <param name="filePath">csv文件路径</param>
        /// <param name="hasHeader">第一行是否为表头</param>
        /// <param name="encoding">编码格式</param>
        /// <returns></returns>
        public static DataTable ReadCsv(string filePath, bool hasHeader, Encoding encoding = null)
        {
            DataTable dataTable = new DataTable();
            using (var reader = new StreamReader(filePath, encoding ?? Encoding.Default))
            {
                string line = reader.ReadLine();
                string[] headers = line.Split(',');
                if (hasHeader)
                {
                    foreach (var header in headers)
                    {
                        dataTable.Columns.Add(header.Trim());
                    }
                }
                else
                {
                    for (int i = 0; i < headers.Length; i++)
                    {
                        dataTable.Columns.Add("Column" + (i + 1));
                    }
                    dataTable.Rows.Add(headers);
                }

                while ((line = reader.ReadLine()) != null)
                {
                    string[] values = line.Split(',');
                    dataTable.Rows.Add(values);
                }
            }
            return dataTable;
        }
    }
}